geometry_columns / geography_column table fails to take into account user permissions to table

[chander]

The current geometry_columns table often lists tables that might not exist for a user (or might not be visible to them) because it fails to take into account the users current search path.

I believe the SQL code for the view should have the following appended to it:

and pg_catalog.pg_table_is_visible(c.oid)

This would result in spatial_ref_sys only displaying information for tables that exist in the current search_path.

[strk]

I wouldn't like not to find information about spatial tables that exist but are not visible with my search_path. What's the rationale for that ? Are there other examples in the standard catalogues ?

[robe]

The geometry_columns view should follow the permission of information_schema.tables.

Meaning, we don't care about current search path as long as the user in question has SELECT, DELETE,UPDATE, or INSERT access to a table.

Even if a table is not in the current search path, as user can explicitly select it by explicitly qualifying with the schema name, which many people prefer doing.

That said it does seem like we are missing the:

has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)

criteria for both geometry_columns and geography_columns. Not sure why we left that out (perhaps the name is different in 8.4), since I vaguely remember this coming up in conversation.

[chander]

Just to rephrase the previous comment of strk (without the double negative):

I would like to find information about spatial tables that exist but are not visible with my search_path. What's the rationale for that ? Are there other examples in the standard catalogues ? —

It seems to me that logically, the geometry_columns table should follow the conventions used by other metadata commands (like \dt, etc.)

The /dt meta-command, or any of the "describe" type commands for example - show you the tables that are visible to your current user. Various tools get confused if you don't do this. A great example is pgsql2shp - which breaks because it queries spatial_ref_sys using only the table name, and expects the search_path to be respected (which would result, for example, in a table not appearing twice in two different schemas.)

Also, tools like pgsql2shp, django (introspection), and others break if two tables with the same name exist in a single database, because they expect that a query of spatial_ref_sys will only show visible table information.

Consider a tool that queries the geometry_columns table to get a list of available tables with geometry data. Is it reasonable to present those tables to the tool if the user won't be able to view them anyway (or shouldn't be able to view them?)

The general expectation (when querying geometry_columns) of tools that query this table, IMHO is that they will see tables that are visible to the current logged_in user.

[chander]

Sorry, I mentioned spatial_ref_sys in the previous comment, where I meant to say geometry_columns.

[robe]

chander,

I think currently visible and accessible are different things.

I don't think strk is disagreeing with you that you shouldn't be able to see tables you don't have access to, but we don't want to limit what you can select by what is in your search path. That would screw a lot of people including me

For example I have a database I have broken out by department:

assessing, parks, political etc.

My user in question has access to all tables, but what is in their search path is their department.

To get say parcels from assessing, they would do

SELECT a,b,c FROM assessing.parcels;

Since they have access to assessing.parcels, that needs to be listed in geometry_columns. for their own department since they use it a lot, they don't care to schema qualify — that's a hassle.

I'm not sure what you mean by pgsql2shp and django breaking with same named tables.

I for one know that pgsql2shp does not break with same named tables. It will pick what's in your search path and if its not you need to explicitly qualify the schema.

Like I said information_schema.tables is a perfect example of a meta table, but that one doesn't care about current search path, HOWEVER only lists tables a user has access to.

[chander]

Ahh, that makes more sense.

Django seems to be schema-unaware, and therefore sees multiple columns when it tries to introspect a table, and pgsql2shp seems to have other issues - which I'm now finding might be related to the way it introspects table definitions (see ticket #1814)

[strk]

has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)

works fine in 8.4 (just tested). I guess it was left out because the old geometry_columns did show everything ?

[strk]

r9900 in 2.0 branch, r9901 in trunk.